{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d006b2ea-9dfe-49c7-88a9-a5a0775185fd",
   "metadata": {},
   "source": [
    "# 🏥 RoboCare AI Assistant\n",
    "\n",
    "## Why I Built This\n",
    "\n",
    "While working on a caregiver matching platform for **MyWoosah Inc** in the US, I faced a real challenge: how do you efficiently match families with the right caregivers when everyone has different needs?\n",
    "\n",
    "Families would ask things like:\n",
    "- *\"I need someone for my mom on Monday mornings who speaks Spanish\"*\n",
    "- *\"Can you find elder care in Boston under $30/hour with CPR certification?\"*\n",
    "\n",
    "Writing individual SQL queries for every combination of filters was exhausting and error-prone. I knew there had to be a better way.\n",
    "\n",
    "That's when I discovered the **Andela LLM Engineering program**. I saw an opportunity to transform this problem into a solution using AI. Instead of rigid queries, what if families could just... talk? And the AI would understand, search, and recommend?\n",
    "\n",
    "This project is my answer. It's not just an exercise—it's solving a real problem I encountered in the field.\n",
    "\n",
    "---\n",
    "\n",
    "## What This Does\n",
    "\n",
    "RoboCare helps families find caregivers through natural conversation. You tell it what you need, and it:\n",
    "- 🔍 Searches the database intelligently\n",
    "- 🎯 Finds the best matches\n",
    "- 💬 Explains pros/cons in plain English  \n",
    "- 🔊 Speaks the results back to you\n",
    "\n",
    "**Tech:** OpenAI GPT-4o + Voice • Gradio UI • SQLite Database • Function Calling\n",
    "\n",
    "---\n",
    "\n",
    "**Note:** This is a demonstration. Always verify credentials independently."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4381c40c",
   "metadata": {},
   "source": [
    "## Step 1: Libraries\n",
    "\n",
    "The essentials: OpenAI for the AI brain, Gradio for the interface, SQLite for data storage.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "185c6841",
   "metadata": {},
   "outputs": [],
   "source": [
    "# imports\n",
    "\n",
    "import os\n",
    "from dotenv import load_dotenv\n",
    "from openai import OpenAI\n",
    "import gradio as gr\n",
    "import sqlite3\n",
    "import sqlite3\n",
    "from textwrap import dedent\n",
    "from contextlib import contextmanager\n",
    "from typing import Optional, List, Dict, Any, Tuple"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a366c15",
   "metadata": {},
   "source": [
    "## Step 2: Setup\n",
    "\n",
    "Loading API keys securely (never hardcode them!), setting up the OpenAI client, and pointing to our database.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "0e731b96",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OpenAI API Key exists and begins sk-proj-\n"
     ]
    }
   ],
   "source": [
    "# Initialization\n",
    "\n",
    "load_dotenv(override=True)\n",
    "\n",
    "openai_api_key = os.getenv('OPENAI_API_KEY')\n",
    "if openai_api_key:\n",
    "    print(f\"OpenAI API Key exists and begins {openai_api_key[:8]}\")\n",
    "else:\n",
    "    print(\"OpenAI API Key not set\")\n",
    "    \n",
    "MODEL = \"gpt-4o-mini\"\n",
    "openai = OpenAI()\n",
    "\n",
    "DB_PATH = \"care_app.db\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "686fa27a",
   "metadata": {},
   "source": [
    "## Step 3: The Database\n",
    "\n",
    "20 sample caregivers across major US cities with:\n",
    "- Services they offer (elder care, child care, etc.)\n",
    "- Languages, certifications, availability\n",
    "- Personality traits\n",
    "- Realistic pricing and schedules\n",
    "\n",
    "This mirrors the kind of data MyWoosah Inc would manage—except here, AI does the matching work.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "965d273d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Seeded: care_app.db\n"
     ]
    }
   ],
   "source": [
    "# Table creation and seeding\n",
    "\n",
    "SQL = '''\n",
    "\n",
    "  CREATE TABLE IF NOT EXISTS caregivers (\n",
    "    id               INTEGER PRIMARY KEY,\n",
    "    name             TEXT NOT NULL,\n",
    "    gender           TEXT,\n",
    "    years_experience INTEGER,\n",
    "    live_in          INTEGER,      -- 0/1\n",
    "    hourly_rate      REAL,\n",
    "    currency         TEXT,\n",
    "    city             TEXT,\n",
    "    state_province   TEXT,\n",
    "    country          TEXT,\n",
    "    postal_code      TEXT,\n",
    "    lat              REAL,\n",
    "    lon              REAL\n",
    "  );\n",
    "\n",
    "  CREATE TABLE IF NOT EXISTS caregiver_services (\n",
    "    caregiver_id INTEGER,\n",
    "    care_type    TEXT,\n",
    "    FOREIGN KEY (caregiver_id) REFERENCES caregivers(id)\n",
    "  );\n",
    "\n",
    "  CREATE TABLE IF NOT EXISTS availability (\n",
    "    caregiver_id INTEGER,\n",
    "    day          TEXT,     -- e.g., 'Mon'\n",
    "    time_start   TEXT,     -- 'HH:MM'\n",
    "    time_end     TEXT,     -- 'HH:MM'\n",
    "    FOREIGN KEY (caregiver_id) REFERENCES caregivers(id)\n",
    "  );\n",
    "\n",
    "  CREATE TABLE IF NOT EXISTS languages (\n",
    "    caregiver_id INTEGER,\n",
    "    language     TEXT,\n",
    "    FOREIGN KEY (caregiver_id) REFERENCES caregivers(id)\n",
    "  );\n",
    "\n",
    "  CREATE TABLE IF NOT EXISTS certifications (\n",
    "    caregiver_id INTEGER,\n",
    "    cert         TEXT,\n",
    "    FOREIGN KEY (caregiver_id) REFERENCES caregivers(id)\n",
    "  );\n",
    "\n",
    "  CREATE TABLE IF NOT EXISTS traits (\n",
    "    caregiver_id INTEGER,\n",
    "    trait        TEXT,\n",
    "    FOREIGN KEY (caregiver_id) REFERENCES caregivers(id)\n",
    "  );\n",
    "\n",
    "  ----------------------------------------------------------\n",
    "\n",
    "  -- Clear old data (optional)\n",
    "\n",
    "  DELETE FROM traits;\n",
    "  DELETE FROM certifications;\n",
    "  DELETE FROM languages;\n",
    "  DELETE FROM availability;\n",
    "  DELETE FROM caregiver_services;\n",
    "  DELETE FROM caregivers;\n",
    "\n",
    "  -- Seed caregivers (20 examples, all USA)\n",
    "\n",
    "  INSERT INTO caregivers\n",
    "  (id, name, gender, years_experience, live_in, hourly_rate, currency, city, state_province, country, postal_code, lat, lon)\n",
    "  VALUES\n",
    "  (1,  'Grace Williams',     'female', 6,  0, 28, 'USD', 'New York',       'NY', 'USA', '10001',  40.7128,  -74.0060),\n",
    "  (2,  'Miguel Alvarez',     'male',   9,  1, 30, 'USD', 'Los Angeles',    'CA', 'USA', '90012',  34.0522, -118.2437),\n",
    "  (3,  'Ava Johnson',        'female', 4,  0, 24, 'USD', 'Chicago',        'IL', 'USA', '60601',  41.8781,  -87.6298),\n",
    "  (4,  'Noah Robinson',      'male',   12, 0, 27, 'USD', 'Houston',        'TX', 'USA', '77002',  29.7604,  -95.3698),\n",
    "  (5,  'Sophia Martinez',    'female', 8,  0, 29, 'USD', 'Phoenix',        'AZ', 'USA', '85004',  33.4484, -112.0740),\n",
    "  (6,  'Daniel Carter',      'male',   10, 1, 31, 'USD', 'Philadelphia',   'PA', 'USA', '19103',  39.9526,  -75.1652),\n",
    "  (7,  'Emily Nguyen',       'female', 7,  0, 26, 'USD', 'San Antonio',    'TX', 'USA', '78205',  29.4241,  -98.4936),\n",
    "  (8,  'Olivia Kim',         'female', 5,  0, 27, 'USD', 'San Diego',      'CA', 'USA', '92101',  32.7157, -117.1611),\n",
    "  (9,  'James Thompson',     'male',   15, 1, 34, 'USD', 'Dallas',         'TX', 'USA', '75201',  32.7767,  -96.7970),\n",
    "  (10, 'Isabella Garcia',    'female', 3,  0, 22, 'USD', 'San Jose',       'CA', 'USA', '95113',  37.3382, -121.8863),\n",
    "  (11, 'Ethan Patel',        'male',   11, 1, 33, 'USD', 'Austin',         'TX', 'USA', '78701',  30.2672,  -97.7431),\n",
    "  (12, 'Harper Brooks',      'female', 2,  0, 20, 'USD', 'Jacksonville',   'FL', 'USA', '32202',  30.3322,  -81.6557),\n",
    "  (13, 'Logan White',        'male',   6,  0, 25, 'USD', 'Fort Worth',     'TX', 'USA', '76102',  32.7555,  -97.3308),\n",
    "  (14, 'Amelia Davis',       'female', 9,  0, 28, 'USD', 'Columbus',       'OH', 'USA', '43215',  39.9612,  -82.9988),\n",
    "  (15, 'Charlotte Reed',     'female', 14, 1, 32, 'USD', 'Charlotte',      'NC', 'USA', '28202',  35.2271,  -80.8431),\n",
    "  (16, 'Jackson Lee',        'male',   5,  0, 26, 'USD', 'San Francisco',  'CA', 'USA', '94102',  37.7749, -122.4194),\n",
    "  (17, 'Avery Chen',         'female', 7,  0, 27, 'USD', 'Seattle',        'WA', 'USA', '98101',  47.6062, -122.3321),\n",
    "  (18, 'William Turner',     'male',   13, 1, 35, 'USD', 'Denver',         'CO', 'USA', '80202',  39.7392, -104.9903),\n",
    "  (19, 'Natalie O''Brien',   'female', 16, 0, 36, 'USD', 'Boston',         'MA', 'USA', '02108',  42.3601,  -71.0589),\n",
    "  (20, 'Maya Robinson',      'female', 3,  0, 23, 'USD', 'Atlanta',        'GA', 'USA', '30303',  33.7488,  -84.3880);\n",
    "\n",
    "  -- Seed caregiver services\n",
    "\n",
    "  INSERT INTO caregiver_services (caregiver_id, care_type) VALUES\n",
    "  (1,  'elder care'),           (1,  'companionship'),\n",
    "  (2,  'post-op support'),      (2,  'elder care'),\n",
    "  (3,  'child care'),           (3,  'special needs'),\n",
    "  (4,  'respite care'),         (4,  'elder care'),\n",
    "  (5,  'dementia care'),        (5,  'companionship'),\n",
    "  (6,  'elder care'),           (6,  'hospice support'),\n",
    "  (7,  'child care'),           (7,  'respite care'),\n",
    "  (8,  'post-op support'),      (8,  'companionship'),\n",
    "  (9,  'special needs'),        (9,  'elder care'),\n",
    "  (10, 'child care'),           (10, 'companionship'),\n",
    "  (11, 'dementia care'),        (11, 'post-op support'),\n",
    "  (12, 'child care'),           (12, 'special needs'),\n",
    "  (13, 'respite care'),         (13, 'companionship'),\n",
    "  (14, 'elder care'),           (14, 'post-op support'),\n",
    "  (15, 'hospice support'),      (15, 'dementia care'),\n",
    "  (16, 'elder care'),           (16, 'respite care'),\n",
    "  (17, 'special needs'),        (17, 'companionship'),\n",
    "  (18, 'post-op support'),      (18, 'elder care'),\n",
    "  (19, 'dementia care'),        (19, 'hospice support'),\n",
    "  (20, 'child care'),           (20, 'companionship');\n",
    "\n",
    "  -- Seed availability (Mon-Sun samples)\n",
    "\n",
    "  INSERT INTO availability (caregiver_id, day, time_start, time_end) VALUES\n",
    "  -- 1 Grace (NY): evenings + Sun\n",
    "  (1, 'Mon', '17:30', '22:00'),\n",
    "  (1, 'Thu', '17:30', '22:00'),\n",
    "  (1, 'Sun', '10:00', '16:00'),\n",
    "  -- 2 Miguel (LA): live-in, long blocks\n",
    "  (2, 'Tue', '08:00', '20:00'),\n",
    "  (2, 'Thu', '08:00', '20:00'),\n",
    "  (2, 'Sat', '09:00', '18:00'),\n",
    "  -- 3 Ava (CHI): weekdays 09-17\n",
    "  (3, 'Mon', '09:00', '17:00'),\n",
    "  (3, 'Wed', '09:00', '17:00'),\n",
    "  (3, 'Fri', '09:00', '17:00'),\n",
    "  -- 4 Noah (HOU): Tue-Fri 08-16\n",
    "  (4, 'Tue', '08:00', '16:00'),\n",
    "  (4, 'Wed', '08:00', '16:00'),\n",
    "  (4, 'Thu', '08:00', '16:00'),\n",
    "  -- 5 Sophia (PHX): Thu-Sun 10-18\n",
    "  (5, 'Thu', '10:00', '18:00'),\n",
    "  (5, 'Fri', '10:00', '18:00'),\n",
    "  (5, 'Sat', '10:00', '18:00'),\n",
    "  -- 6 Daniel (PHL): Mon-Thu 07-15\n",
    "  (6, 'Mon', '07:00', '15:00'),\n",
    "  (6, 'Tue', '07:00', '15:00'),\n",
    "  (6, 'Thu', '07:00', '15:00'),\n",
    "  -- 7 Emily (SAT): weekends\n",
    "  (7, 'Sat', '08:00', '17:00'),\n",
    "  (7, 'Sun', '09:00', '17:00'),\n",
    "  (7, 'Fri', '17:00', '21:00'),\n",
    "  -- 8 Olivia (SD): Mon, Wed evenings\n",
    "  (8, 'Mon', '16:00', '21:00'),\n",
    "  (8, 'Wed', '16:00', '21:00'),\n",
    "  (8, 'Sat', '10:00', '14:00'),\n",
    "  -- 9 James (DAL): live-in wide\n",
    "  (9, 'Mon', '07:00', '19:00'),\n",
    "  (9, 'Wed', '07:00', '19:00'),\n",
    "  (9, 'Sun', '09:00', '17:00'),\n",
    "  -- 10 Isabella (SJ): Tue-Thu 12-20\n",
    "  (10, 'Tue', '12:00', '20:00'),\n",
    "  (10, 'Wed', '12:00', '20:00'),\n",
    "  (10, 'Thu', '12:00', '20:00'),\n",
    "  -- 11 Ethan (ATX): nights\n",
    "  (11, 'Mon', '18:00', '23:00'),\n",
    "  (11, 'Tue', '18:00', '23:00'),\n",
    "  (11, 'Fri', '18:00', '23:00'),\n",
    "  -- 12 Harper (JAX): school hours\n",
    "  (12, 'Mon', '09:00', '14:00'),\n",
    "  (12, 'Wed', '09:00', '14:00'),\n",
    "  (12, 'Fri', '09:00', '14:00'),\n",
    "  -- 13 Logan (FTW): Thu-Sat\n",
    "  (13, 'Thu', '10:00', '18:00'),\n",
    "  (13, 'Fri', '10:00', '18:00'),\n",
    "  (13, 'Sat', '10:00', '18:00'),\n",
    "  -- 14 Amelia (CMH): Mon-Fri 08-16\n",
    "  (14, 'Mon', '08:00', '16:00'),\n",
    "  (14, 'Tue', '08:00', '16:00'),\n",
    "  (14, 'Thu', '08:00', '16:00'),\n",
    "  -- 15 Charlotte (CLT): live-in style\n",
    "  (15, 'Tue', '07:00', '19:00'),\n",
    "  (15, 'Thu', '07:00', '19:00'),\n",
    "  (15, 'Sat', '08:00', '16:00'),\n",
    "  -- 16 Jackson (SF): split shifts\n",
    "  (16, 'Mon', '07:00', '11:00'),\n",
    "  (16, 'Mon', '17:00', '21:00'),\n",
    "  (16, 'Sat', '12:00', '18:00'),\n",
    "  -- 17 Avery (SEA): Tue/Thu + Sun\n",
    "  (17, 'Tue', '10:00', '18:00'),\n",
    "  (17, 'Thu', '10:00', '18:00'),\n",
    "  (17, 'Sun', '11:00', '17:00'),\n",
    "  -- 18 William (DEN): Mon-Wed 06-14\n",
    "  (18, 'Mon', '06:00', '14:00'),\n",
    "  (18, 'Tue', '06:00', '14:00'),\n",
    "  (18, 'Wed', '06:00', '14:00'),\n",
    "  -- 19 Natalie (BOS): Tue-Fri 09-17\n",
    "  (19, 'Tue', '09:00', '17:00'),\n",
    "  (19, 'Wed', '09:00', '17:00'),\n",
    "  (19, 'Fri', '09:00', '17:00'),\n",
    "  -- 20 Maya (ATL): after-school + Sat\n",
    "  (20, 'Mon', '15:00', '20:00'),\n",
    "  (20, 'Wed', '15:00', '20:00'),\n",
    "  (20, 'Sat', '09:00', '15:00');\n",
    "\n",
    "  -- Seed languages\n",
    "\n",
    "  INSERT INTO languages (caregiver_id, language) VALUES\n",
    "  (1, 'English'),      (1, 'Spanish'),\n",
    "  (2, 'English'),      (2, 'Spanish'),\n",
    "  (3, 'English'),\n",
    "  (4, 'English'),\n",
    "  (5, 'English'),      (5, 'Spanish'),\n",
    "  (6, 'English'),\n",
    "  (7, 'English'),      (7, 'Vietnamese'),\n",
    "  (8, 'English'),      (8, 'Korean'),\n",
    "  (9, 'English'),\n",
    "  (10,'English'),      (10,'Spanish'),\n",
    "  (11,'English'),      (11,'Hindi'),\n",
    "  (12,'English'),\n",
    "  (13,'English'),\n",
    "  (14,'English'),      (14,'French'),\n",
    "  (15,'English'),\n",
    "  (16,'English'),      (16,'Tagalog'),\n",
    "  (17,'English'),      (17,'Mandarin'),\n",
    "  (18,'English'),\n",
    "  (19,'English'),      (19,'Portuguese'),\n",
    "  (20,'English'),      (20,'ASL');\n",
    "\n",
    "  -- Seed certifications\n",
    "\n",
    "  INSERT INTO certifications (caregiver_id, cert) VALUES\n",
    "  (1, 'CPR'),                 (1, 'First Aid'),\n",
    "  (2, 'CPR'),                 (2, 'BLS'),\n",
    "  (3, 'CPR'),\n",
    "  (4, 'First Aid'),           (4, 'CNA'),\n",
    "  (5, 'CPR'),                 (5, 'Dementia Care'),\n",
    "  (6, 'HHA'),                 (6, 'CPR'),\n",
    "  (7, 'First Aid'),\n",
    "  (8, 'CPR'),                 (8, 'AED'),\n",
    "  (9, 'CNA'),                 (9, 'BLS'),\n",
    "  (10,'First Aid'),\n",
    "  (11,'CPR'),                 (11,'Medication Technician'),\n",
    "  (12,'CPR'),\n",
    "  (13,'First Aid'),\n",
    "  (14,'CPR'),                 (14,'CNA'),\n",
    "  (15,'Hospice Training'),    (15,'CPR'),\n",
    "  (16,'First Aid'),\n",
    "  (17,'CPR'),                 (17,'Special Needs Training'),\n",
    "  (18,'BLS'),                 (18,'CPR'),\n",
    "  (19,'Dementia Care'),       (19,'First Aid'),\n",
    "  (20,'CPR'),                 (20,'Childcare Safety');\n",
    "\n",
    "  -- Seed traits\n",
    "\n",
    "  INSERT INTO traits (caregiver_id, trait) VALUES\n",
    "  (1, 'empathetic'),          (1, 'detail-oriented'),\n",
    "  (2, 'patient'),             (2, 'communicative'),\n",
    "  (3, 'cheerful'),            (3, 'reliable'),\n",
    "  (4, 'organized'),           (4, 'professional'),\n",
    "  (5, 'compassionate'),       (5, 'trustworthy'),\n",
    "  (6, 'calm under pressure'), (6, 'punctual'),\n",
    "  (7, 'adaptable'),           (7, 'energetic'),\n",
    "  (8, 'friendly'),            (8, 'respectful'),\n",
    "  (9, 'thorough'),            (9, 'dependable'),\n",
    "  (10,'gentle'),              (10,'attentive'),\n",
    "  (11,'proactive'),           (11,'communicative'),\n",
    "  (12,'patient'),             (12,'kind'),\n",
    "  (13,'flexible'),            (13,'tidy'),\n",
    "  (14,'reliable'),            (14,'punctual'),\n",
    "  (15,'compassionate'),       (15,'detail-oriented'),\n",
    "  (16,'discreet'),            (16,'organized'),\n",
    "  (17,'empathetic'),          (17,'calm under pressure'),\n",
    "  (18,'professional'),        (18,'thorough'),\n",
    "  (19,'trustworthy'),         (19,'proactive'),\n",
    "  (20,'cheerful'),            (20,'attentive');\n",
    "\n",
    "'''\n",
    "\n",
    "# Insert the data into the database\n",
    "\n",
    "sql = dedent(SQL)\n",
    "con = sqlite3.connect(DB_PATH)\n",
    "con.executescript(sql)\n",
    "con.commit()\n",
    "con.close()\n",
    "print(\"Seeded:\", DB_PATH)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c0baa64",
   "metadata": {},
   "source": [
    "## Step 4: Teaching the AI to Search\n",
    "\n",
    "Instead of the AI just talking, we teach it to actually *search* the database.\n",
    "\n",
    "When someone says *\"I need elder care in Boston for Mondays\"*, the AI translates that into:\n",
    "```python\n",
    "search_caregivers(city=\"Boston\", care_type=\"elder care\", day=\"Mon\")\n",
    "```\n",
    "\n",
    "This schema defines all the filters the AI can use: location, services, budget, language, availability, and more.\n",
    "\n",
    "**This was the breakthrough.** No more writing custom queries—the AI figures it out.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "f2af7c67",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'type': 'function',\n",
       "  'function': {'name': 'search_caregivers',\n",
       "   'description': 'Flexible multi-filter caregiver search. Any filter can be omitted. Supports location, service type, experience, pricing, live-in, language, certifications, day/time availability, and pagination.',\n",
       "   'parameters': {'type': 'object',\n",
       "    'properties': {'city': {'type': 'string',\n",
       "      'description': 'City name to filter by (optional).'},\n",
       "     'state_province': {'type': 'string',\n",
       "      'description': 'State or province to filter by (optional).'},\n",
       "     'country': {'type': 'string',\n",
       "      'description': 'Country to filter by (optional).'},\n",
       "     'care_type': {'type': 'string',\n",
       "      'description': \"Service category, e.g., 'elder_care', 'child_care', 'pet_care', 'housekeeping' (optional).\"},\n",
       "     'min_experience': {'type': 'integer',\n",
       "      'minimum': 0,\n",
       "      'description': 'Minimum years of experience (optional).'},\n",
       "     'max_hourly_rate': {'type': 'number',\n",
       "      'minimum': 0,\n",
       "      'description': 'Maximum hourly rate in local currency (optional).'},\n",
       "     'live_in': {'type': 'boolean',\n",
       "      'description': 'Require live-in caregivers (optional).'},\n",
       "     'language': {'type': 'string',\n",
       "      'description': \"Required spoken language, e.g., 'English', 'Spanish' (optional).\"},\n",
       "     'certification': {'type': 'string',\n",
       "      'description': \"Required certification, e.g., 'CPR', 'CNA' (optional).\"},\n",
       "     'day': {'type': 'string',\n",
       "      'description': \"Day of week to match availability (optional), e.g., 'Monday', 'Tuesday', ... 'Sunday'.\"},\n",
       "     'time_between': {'type': 'array',\n",
       "      'description': \"Required availability window as ['HH:MM','HH:MM'] in 24h time. Matches caregivers whose availability window fully covers this range.\",\n",
       "      'items': {'type': 'string',\n",
       "       'pattern': '^\\\\d{2}:\\\\d{2}$',\n",
       "       'description': \"Time in 'HH:MM' 24-hour format.\"},\n",
       "      'minItems': 2,\n",
       "      'maxItems': 2},\n",
       "     'limit': {'type': 'integer',\n",
       "      'minimum': 1,\n",
       "      'maximum': 1000,\n",
       "      'default': 50,\n",
       "      'description': 'Max number of results to return (default 50).'},\n",
       "     'offset': {'type': 'integer',\n",
       "      'minimum': 0,\n",
       "      'default': 0,\n",
       "      'description': 'Number of results to skip for pagination (default 0).'}},\n",
       "    'required': [],\n",
       "    'additionalProperties': False}}}]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Tool definition schema\n",
    "\n",
    "tools = [{\n",
    "    \"type\": \"function\",\n",
    "    \"function\": {\n",
    "        \"name\": \"search_caregivers\",\n",
    "        \"description\": (\n",
    "            \"Flexible multi-filter caregiver search. Any filter can be omitted. \"\n",
    "            \"Supports location, service type, experience, pricing, live-in, language, \"\n",
    "            \"certifications, day/time availability, and pagination.\"\n",
    "        ),\n",
    "        \"parameters\": {\n",
    "            \"type\": \"object\",\n",
    "            \"properties\": {\n",
    "                \"city\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": \"City name to filter by (optional).\"\n",
    "                },\n",
    "                \"state_province\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": \"State or province to filter by (optional).\"\n",
    "                },\n",
    "                \"country\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": \"Country to filter by (optional).\"\n",
    "                },\n",
    "                \"care_type\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": (\n",
    "                        \"Service category, e.g., 'elder_care', 'child_care', \"\n",
    "                        \"'pet_care', 'housekeeping' (optional).\"\n",
    "                    )\n",
    "                },\n",
    "                \"min_experience\": {\n",
    "                    \"type\": \"integer\",\n",
    "                    \"minimum\": 0,\n",
    "                    \"description\": \"Minimum years of experience (optional).\"\n",
    "                },\n",
    "                \"max_hourly_rate\": {\n",
    "                    \"type\": \"number\",\n",
    "                    \"minimum\": 0,\n",
    "                    \"description\": \"Maximum hourly rate in local currency (optional).\"\n",
    "                },\n",
    "                \"live_in\": {\n",
    "                    \"type\": \"boolean\",\n",
    "                    \"description\": \"Require live-in caregivers (optional).\"\n",
    "                },\n",
    "                \"language\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": \"Required spoken language, e.g., 'English', 'Spanish' (optional).\"\n",
    "                },\n",
    "                \"certification\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": \"Required certification, e.g., 'CPR', 'CNA' (optional).\"\n",
    "                },\n",
    "                \"day\": {\n",
    "                    \"type\": \"string\",\n",
    "                    \"description\": (\n",
    "                        \"Day of week to match availability (optional), e.g., \"\n",
    "                        \"'Monday', 'Tuesday', ... 'Sunday'.\"\n",
    "                    )\n",
    "                },\n",
    "                \"time_between\": {\n",
    "                    \"type\": \"array\",\n",
    "                    \"description\": (\n",
    "                        \"Required availability window as ['HH:MM','HH:MM'] in 24h time. \"\n",
    "                        \"Matches caregivers whose availability window fully covers this range.\"\n",
    "                    ),\n",
    "                    \"items\": {\n",
    "                        \"type\": \"string\",\n",
    "                        \"pattern\": \"^\\\\d{2}:\\\\d{2}$\",\n",
    "                        \"description\": \"Time in 'HH:MM' 24-hour format.\"\n",
    "                    },\n",
    "                    \"minItems\": 2,\n",
    "                    \"maxItems\": 2\n",
    "                },\n",
    "                \"limit\": {\n",
    "                    \"type\": \"integer\",\n",
    "                    \"minimum\": 1,\n",
    "                    \"maximum\": 1000,\n",
    "                    \"default\": 50,\n",
    "                    \"description\": \"Max number of results to return (default 50).\"\n",
    "                },\n",
    "                \"offset\": {\n",
    "                    \"type\": \"integer\",\n",
    "                    \"minimum\": 0,\n",
    "                    \"default\": 0,\n",
    "                    \"description\": \"Number of results to skip for pagination (default 0).\"\n",
    "                }\n",
    "            },\n",
    "            \"required\": [],\n",
    "            \"additionalProperties\": False\n",
    "        }\n",
    "    }\n",
    "}]\n",
    "\n",
    "tools"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76416da2",
   "metadata": {},
   "source": [
    "## Step 5: Helper Functions\n",
    "\n",
    "**Voice:** The AI can speak its responses using OpenAI's text-to-speech.\n",
    "\n",
    "**Database functions:** All the queries we need—search, get profiles, check availability, etc. These are what the AI calls behind the scenes.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "2f50cc15",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Convert text to speech using OpenAI's TTS API\n",
    "def announcements(message):\n",
    "    response = openai.audio.speech.create(\n",
    "      model=\"gpt-4o-mini-tts\",\n",
    "      voice=\"coral\",    # Also, try replacing onyx with alloy or coral\n",
    "      input=message\n",
    "    )\n",
    "    return response.content\n",
    "\n",
    "# Context manager for database connection\n",
    "@contextmanager\n",
    "def _conn(dict_rows: bool = True):\n",
    "    conn = sqlite3.connect(DB_PATH)\n",
    "    if dict_rows:\n",
    "        conn.row_factory = _dict_factory\n",
    "    try:\n",
    "        yield conn\n",
    "        conn.commit()\n",
    "    finally:\n",
    "        conn.close()\n",
    "\n",
    "####################\n",
    "# Helper functions #\n",
    "####################\n",
    "\n",
    "# Converts SQLite query results from tuples into dictionaries\n",
    "def _dict_factory(cursor, row):\n",
    "    return {col[0]: row[idx] for idx, col in enumerate(cursor.description)}\n",
    "# A debug/logging function that prints database tool activity\n",
    "def _print(msg: str):\n",
    "    print(f\"DATABASE TOOL CALLED: {msg}\", flush=True)\n",
    "\n",
    "################################\n",
    "# Caregiver database functions #\n",
    "################################\n",
    "\n",
    "# Counts the number of caregivers in the database\n",
    "def get_caregiver_count() -> int:\n",
    "    _print(\"Counting caregivers\")\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT COUNT(*) AS n FROM caregivers\")\n",
    "        return cur.fetchone()[\"n\"]\n",
    "\n",
    "# Fetches a caregiver's profile by their ID\n",
    "def get_caregiver(caregiver_id: int) -> Optional[Dict[str, Any]]:\n",
    "    _print(f\"Fetching caregiver #{caregiver_id}\")\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"SELECT * FROM caregivers WHERE id = ?\", (caregiver_id,))\n",
    "        return cur.fetchone()\n",
    "\n",
    "# Lists caregivers with pagination\n",
    "def list_caregivers(limit: int = 20, offset: int = 0) -> List[Dict[str, Any]]:\n",
    "    _print(f\"Listing caregivers (limit={limit}, offset={offset})\")\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"\"\"\n",
    "            SELECT * FROM caregivers\n",
    "            ORDER BY id\n",
    "            LIMIT ? OFFSET ?\n",
    "        \"\"\", (limit, offset))\n",
    "        return cur.fetchall()\n",
    "\n",
    "# Fetches the services a caregiver offers\n",
    "def get_services(caregiver_id: int) -> List[str]:\n",
    "    _print(f\"Fetching services for caregiver #{caregiver_id}\")\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"\"\"\n",
    "            SELECT care_type FROM caregiver_services WHERE caregiver_id = ?\n",
    "            ORDER BY care_type\n",
    "        \"\"\", (caregiver_id,))\n",
    "        return [r[\"care_type\"] for r in cur.fetchall()]\n",
    "\n",
    "# Fetches the languages a caregiver speaks\n",
    "def get_languages(caregiver_id: int) -> List[str]:\n",
    "    _print(f\"Fetching languages for caregiver #{caregiver_id}\")\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"\"\"\n",
    "            SELECT language FROM languages WHERE caregiver_id = ?\n",
    "            ORDER BY language\n",
    "        \"\"\", (caregiver_id,))\n",
    "        return [r[\"language\"] for r in cur.fetchall()]\n",
    "\n",
    "# Fetches the certifications a caregiver has\n",
    "def get_certifications(caregiver_id: int) -> List[str]:\n",
    "    _print(f\"Fetching certifications for caregiver #{caregiver_id}\")\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"\"\"\n",
    "            SELECT cert FROM certifications WHERE caregiver_id = ?\n",
    "            ORDER BY cert\n",
    "        \"\"\", (caregiver_id,))\n",
    "        return [r[\"cert\"] for r in cur.fetchall()]\n",
    "\n",
    "# Fetches the traits a caregiver has\n",
    "def get_traits(caregiver_id: int) -> List[str]:\n",
    "    _print(f\"Fetching traits for caregiver #{caregiver_id}\")\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"\"\"\n",
    "            SELECT trait FROM traits WHERE caregiver_id = ?\n",
    "            ORDER BY trait\n",
    "        \"\"\", (caregiver_id,))\n",
    "        return [r[\"trait\"] for r in cur.fetchall()]\n",
    "\n",
    "# Fetches the availability of a caregiver\n",
    "def get_availability(caregiver_id: int) -> List[Dict[str, str]]:\n",
    "    _print(f\"Fetching availability for caregiver #{caregiver_id}\")\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(\"\"\"\n",
    "            SELECT day, time_start, time_end\n",
    "            FROM availability\n",
    "            WHERE caregiver_id = ?\n",
    "            ORDER BY\n",
    "              CASE day\n",
    "                WHEN 'Mon' THEN 1 WHEN 'Tue' THEN 2 WHEN 'Wed' THEN 3\n",
    "                WHEN 'Thu' THEN 4 WHEN 'Fri' THEN 5 WHEN 'Sat' THEN 6\n",
    "                WHEN 'Sun' THEN 7 ELSE 8\n",
    "              END, time_start\n",
    "        \"\"\", (caregiver_id,))\n",
    "        return cur.fetchall()\n",
    "\n",
    "# Fetches a caregiver's full profile\n",
    "def get_caregiver_profile(caregiver_id: int) -> Optional[Dict[str, Any]]:\n",
    "    _print(f\"Fetching full profile for caregiver #{caregiver_id}\")\n",
    "    base = get_caregiver(caregiver_id)\n",
    "    if not base:\n",
    "        return None\n",
    "    base[\"services\"] = get_services(caregiver_id)\n",
    "    base[\"languages\"] = get_languages(caregiver_id)\n",
    "    base[\"certifications\"] = get_certifications(caregiver_id)\n",
    "    base[\"traits\"] = get_traits(caregiver_id)\n",
    "    base[\"availability\"] = get_availability(caregiver_id)\n",
    "    return base\n",
    "\n",
    "###########################################\n",
    "# Search caregivers with multiple filters #\n",
    "###########################################\n",
    "\n",
    "def search_caregivers(\n",
    "    city: Optional[str] = None,\n",
    "    state_province: Optional[str] = None,\n",
    "    country: Optional[str] = None,\n",
    "    care_type: Optional[str] = None,\n",
    "    min_experience: Optional[int] = None,\n",
    "    max_hourly_rate: Optional[float] = None,\n",
    "    live_in: Optional[bool] = None,\n",
    "    language: Optional[str] = None,\n",
    "    certification: Optional[str] = None,\n",
    "    day: Optional[str] = None,\n",
    "    time_between: Optional[Tuple[str, str]] = None,  # ('HH:MM', 'HH:MM')\n",
    "    limit: int = 50,\n",
    "    offset: int = 0\n",
    ") -> List[Dict[str, Any]]:\n",
    "    \"\"\"\n",
    "    Flexible multi-filter search. Any filter can be omitted.\n",
    "    \"\"\"\n",
    "    _print(\"Searching caregivers with multiple filters\")\n",
    "\n",
    "    # base + optional joins\n",
    "    join_clauses = []\n",
    "    where = [\"1=1\"]\n",
    "    params: List[Any] = []\n",
    "\n",
    "    if care_type:\n",
    "        join_clauses.append(\"JOIN caregiver_services s ON s.caregiver_id = c.id\")\n",
    "        where.append(\"LOWER(s.care_type) = LOWER(?)\")\n",
    "        params.append(care_type)\n",
    "\n",
    "    if language:\n",
    "        join_clauses.append(\"JOIN languages l ON l.caregiver_id = c.id\")\n",
    "        where.append(\"LOWER(l.language) = LOWER(?)\")\n",
    "        params.append(language)\n",
    "\n",
    "    if certification:\n",
    "        join_clauses.append(\"JOIN certifications cert ON cert.caregiver_id = c.id\")\n",
    "        where.append(\"LOWER(cert.cert) = LOWER(?)\")\n",
    "        params.append(certification)\n",
    "\n",
    "    if day or time_between:\n",
    "        join_clauses.append(\"JOIN availability a ON a.caregiver_id = c.id\")\n",
    "        if day:\n",
    "            where.append(\"a.day = ?\")\n",
    "            params.append(day)\n",
    "        if time_between:\n",
    "            t0, t1 = time_between\n",
    "            # overlap check: caregiver window [start,end] must include [t0,t1]\n",
    "            where.append(\"a.time_start <= ? AND a.time_end >= ?\")\n",
    "            params.extend([t0, t1])\n",
    "\n",
    "    if city:\n",
    "        where.append(\"LOWER(c.city) = LOWER(?)\")\n",
    "        params.append(city)\n",
    "    if state_province:\n",
    "        where.append(\"LOWER(c.state_province) = LOWER(?)\")\n",
    "        params.append(state_province)\n",
    "    if country:\n",
    "        where.append(\"LOWER(c.country) = LOWER(?)\")\n",
    "        params.append(country)\n",
    "    if min_experience is not None:\n",
    "        where.append(\"c.years_experience >= ?\")\n",
    "        params.append(min_experience)\n",
    "    if max_hourly_rate is not None:\n",
    "        where.append(\"c.hourly_rate <= ?\")\n",
    "        params.append(max_hourly_rate)\n",
    "    if live_in is not None:\n",
    "        where.append(\"c.live_in = ?\")\n",
    "        params.append(1 if live_in else 0)\n",
    "\n",
    "    sql = f\"\"\"\n",
    "        SELECT DISTINCT c.*\n",
    "        FROM caregivers c\n",
    "        {' '.join(join_clauses)}\n",
    "        WHERE {' AND '.join(where)}\n",
    "        ORDER BY c.hourly_rate ASC, c.years_experience DESC, c.id\n",
    "        LIMIT ? OFFSET ?\n",
    "    \"\"\"\n",
    "    params.extend([limit, offset])\n",
    "\n",
    "    with _conn() as conn:\n",
    "        cur = conn.cursor()\n",
    "        cur.execute(sql, tuple(params))\n",
    "        return cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c526d05",
   "metadata": {},
   "source": [
    "## Step 6: Quick Test\n",
    "\n",
    "Before connecting everything to the AI, let's make sure the database works. Run these examples to see sample caregivers and their profiles.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "98165a21",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DATABASE TOOL CALLED: Searching caregivers with multiple filters\n",
      "Found 1 elder care providers in New York:\n",
      "- Grace Williams: $28.0/hr, 6 years experience\n",
      "\n",
      "============================================================\n",
      "\n",
      "DATABASE TOOL CALLED: Searching caregivers with multiple filters\n",
      "Found 1 Spanish-speaking child care providers:\n",
      "- Isabella Garcia in San Jose, CA\n",
      "\n",
      "============================================================\n",
      "\n",
      "DATABASE TOOL CALLED: Fetching full profile for caregiver #1\n",
      "DATABASE TOOL CALLED: Fetching caregiver #1\n",
      "DATABASE TOOL CALLED: Fetching services for caregiver #1\n",
      "DATABASE TOOL CALLED: Fetching languages for caregiver #1\n",
      "DATABASE TOOL CALLED: Fetching certifications for caregiver #1\n",
      "DATABASE TOOL CALLED: Fetching traits for caregiver #1\n",
      "DATABASE TOOL CALLED: Fetching availability for caregiver #1\n",
      "Detailed profile for Grace Williams:\n",
      "  Services: companionship, elder care\n",
      "  Languages: English, Spanish\n",
      "  Certifications: CPR, First Aid\n",
      "  Traits: detail-oriented, empathetic\n",
      "  Availability: 3 time slots\n"
     ]
    }
   ],
   "source": [
    "# Example 1: Search for elder care providers in New York\n",
    "results = search_caregivers(\n",
    "    city=\"New York\",\n",
    "    care_type=\"elder care\",\n",
    "    max_hourly_rate=30.0,\n",
    "    limit=5\n",
    ")\n",
    "\n",
    "print(f\"Found {len(results)} elder care providers in New York:\")\n",
    "for caregiver in results:\n",
    "    print(f\"- {caregiver['name']}: ${caregiver['hourly_rate']}/hr, {caregiver['years_experience']} years experience\")\n",
    "\n",
    "print(\"\\n\" + \"=\"*60 + \"\\n\")\n",
    "\n",
    "# Example 2: Search for Spanish-speaking child care providers\n",
    "results2 = search_caregivers(\n",
    "    care_type=\"child care\",\n",
    "    language=\"Spanish\",\n",
    "    limit=3\n",
    ")\n",
    "\n",
    "print(f\"Found {len(results2)} Spanish-speaking child care providers:\")\n",
    "for caregiver in results2:\n",
    "    print(f\"- {caregiver['name']} in {caregiver['city']}, {caregiver['state_province']}\")\n",
    "\n",
    "print(\"\\n\" + \"=\"*60 + \"\\n\")\n",
    "\n",
    "# Example 3: Get detailed profile of a specific caregiver\n",
    "if results:\n",
    "    caregiver_id = results[0]['id']\n",
    "    profile = get_caregiver_profile(caregiver_id)\n",
    "    print(f\"Detailed profile for {profile['name']}:\")\n",
    "    print(f\"  Services: {', '.join(profile['services'])}\")\n",
    "    print(f\"  Languages: {', '.join(profile['languages'])}\")\n",
    "    print(f\"  Certifications: {', '.join(profile['certifications'])}\")\n",
    "    print(f\"  Traits: {', '.join(profile['traits'])}\")\n",
    "    print(f\"  Availability: {len(profile['availability'])} time slots\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abfa81e6",
   "metadata": {},
   "source": [
    "## Step 7: The AI's Instructions\n",
    "\n",
    "Here's where I learned prompt engineering matters *a lot*.\n",
    "\n",
    "The AI needs to know:\n",
    "- What exact keywords to use (\"elder care\" not \"elderly care\", \"Mon\" not \"Monday\")\n",
    "- How to map natural language to database values\n",
    "- That it should give 2-3 recommendations with pros/cons\n",
    "- To remind families to verify credentials independently\n",
    "\n",
    "**The lesson from MyWoosah:** Small keyword mismatches = zero results. This prompt prevents that.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "7bbe36e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# System prompt\n",
    "\n",
    "system_prompt = '''\n",
    "    You are a compassionate Caregiver Assistant that helps families quickly identify the most\n",
    "    suitable care provider by gathering requirements (care needs, schedule, budget, location,\n",
    "    language/cultural fit) and matching them to available profiles. Provide 2-3 best-fit options\n",
    "    with pros/cons, estimated costs, and next steps, and clearly state that credentials/background\n",
    "    checks are not verified by this sample app and should be confirmed by the family.\n",
    "\n",
    "    CRITICAL: When searching the database, you MUST use these EXACT terms:\n",
    "\n",
    "    CARE TYPES (use exactly as shown):\n",
    "    - \"elder care\" (for elderly, senior, old age, geriatric care)\n",
    "    - \"companionship\" (for companion, friendship, social support)\n",
    "    - \"post-op support\" (for post-surgery, post-operative, recovery care)\n",
    "    - \"child care\" (for children, kids, babysitting, nanny)\n",
    "    - \"special needs\" (for disabilities, autism, developmental needs)\n",
    "    - \"respite care\" (for temporary relief, break for family caregivers)\n",
    "    - \"dementia care\" (for Alzheimer's, memory care, cognitive decline)\n",
    "    - \"hospice support\" (for end-of-life, palliative, terminal care)\n",
    "\n",
    "    If a user mentions any variation, map it to the closest match above. If unclear, ask clarifying questions.\n",
    "\n",
    "    DAYS OF WEEK (use exactly as shown):\n",
    "    - \"Mon\" (for Monday)\n",
    "    - \"Tue\" (for Tuesday)\n",
    "    - \"Wed\" (for Wednesday)\n",
    "    - \"Thu\" (for Thursday)\n",
    "    - \"Fri\" (for Friday)\n",
    "    - \"Sat\" (for Saturday)\n",
    "    - \"Sun\" (for Sunday)\n",
    "\n",
    "    STATES/PROVINCES (use 2-letter codes):\n",
    "    - Use standard US state abbreviations: \"NY\", \"CA\", \"TX\", \"FL\", \"MA\", etc.\n",
    "    - Convert full state names to abbreviations before searching\n",
    "\n",
    "    COMMON LANGUAGES:\n",
    "    - \"English\", \"Spanish\", \"French\", \"Vietnamese\", \"Korean\", \"Hindi\", \"Mandarin\", \"Portuguese\", \"Tagalog\", \"ASL\"\n",
    "    - Capitalize properly (e.g., user says \"spanish\" → use \"Spanish\")\n",
    "\n",
    "    CERTIFICATIONS:\n",
    "    - \"CPR\", \"First Aid\", \"CNA\", \"BLS\", \"HHA\", \"AED\", \"Medication Technician\", \"Hospice Training\", \n",
    "    \"Dementia Care\", \"Special Needs Training\", \"Childcare Safety\"\n",
    "    - Use exact capitalization and full names\n",
    "\n",
    "    TRAITS:\n",
    "    - \"empathetic\", \"patient\", \"cheerful\", \"organized\", \"compassionate\", \"calm under pressure\", \n",
    "    \"adaptable\", \"friendly\", \"thorough\", \"gentle\", \"proactive\", \"flexible\", \"reliable\", \n",
    "    \"detail-oriented\", \"communicative\", \"energetic\", \"respectful\", \"dependable\", \"attentive\", \n",
    "    \"kind\", \"tidy\", \"punctual\", \"discreet\", \"professional\", \"trustworthy\"\n",
    "    - Use lowercase for all traits\n",
    "\n",
    "    SEARCH STRATEGY:\n",
    "    1. Listen carefully to user requirements\n",
    "    2. Map their natural language to database terms above\n",
    "    3. Use search_caregivers() with exact keyword matches\n",
    "    4. If no results, suggest alternatives or broader searches\n",
    "    5. After getting results, use get_caregiver_profile() for detailed information on top matches\n",
    "\n",
    "    Always confirm your understanding by restating requirements using the exact database terms before searching.\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b8ae902",
   "metadata": {},
   "source": [
    "## Step 8: Making it Work (and Not Crash)\n",
    "\n",
    "This is the engine room. When the AI wants to search, this code:\n",
    "1. Validates the request\n",
    "2. Calls the right database function\n",
    "3. Handles errors gracefully (no crashes!)\n",
    "4. Limits results to prevent overwhelming the AI\n",
    "5. Generates the voice response\n",
    "\n",
    "**Defensive programming:** I learned the hard way that things break. This code expects problems and handles them elegantly.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "0d8accbc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function registry: Maps tool names to actual Python functions\n",
    "TOOL_REGISTRY = {\n",
    "    \"search_caregivers\": search_caregivers,\n",
    "    \"get_caregiver_count\": get_caregiver_count,\n",
    "    \"get_caregiver\": get_caregiver,\n",
    "    \"list_caregivers\": list_caregivers,\n",
    "    \"get_services\": get_services,\n",
    "    \"get_languages\": get_languages,\n",
    "    \"get_certifications\": get_certifications,\n",
    "    \"get_traits\": get_traits,\n",
    "    \"get_availability\": get_availability,\n",
    "    \"get_caregiver_profile\": get_caregiver_profile,\n",
    "}\n",
    "\n",
    "def execute_tool_call(tool_call):\n",
    "    \"\"\"\n",
    "    Safely execute a single tool call with error handling.\n",
    "    Returns a properly formatted tool response.\n",
    "    \"\"\"\n",
    "    import json\n",
    "    \n",
    "    function_name = tool_call.function.name\n",
    "    \n",
    "    # Defensive check: Ensure function exists in registry\n",
    "    if function_name not in TOOL_REGISTRY:\n",
    "        return {\n",
    "            \"role\": \"tool\",\n",
    "            \"tool_call_id\": tool_call.id,\n",
    "            \"content\": json.dumps({\n",
    "                \"error\": f\"Unknown function: {function_name}\",\n",
    "                \"available_functions\": list(TOOL_REGISTRY.keys())\n",
    "            })\n",
    "        }\n",
    "    \n",
    "    try:\n",
    "        # Parse arguments\n",
    "        args = json.loads(tool_call.function.arguments)\n",
    "        \n",
    "        # Execute the function\n",
    "        func = TOOL_REGISTRY[function_name]\n",
    "        result = func(**args)\n",
    "        \n",
    "        # Format response based on result type with limit to prevent token overflow\n",
    "        if isinstance(result, list):\n",
    "            content = json.dumps({\n",
    "                \"count\": len(result),\n",
    "                \"results\": result[:10] if len(result) > 10 else result,\n",
    "                \"truncated\": len(result) > 10\n",
    "            })\n",
    "        elif isinstance(result, dict):\n",
    "            content = json.dumps(result)\n",
    "        elif isinstance(result, (int, float, str)):\n",
    "            content = json.dumps({\"result\": result})\n",
    "        else:\n",
    "            content = str(result)\n",
    "            \n",
    "        return {\n",
    "            \"role\": \"tool\",\n",
    "            \"tool_call_id\": tool_call.id,\n",
    "            \"content\": content\n",
    "        }\n",
    "        \n",
    "    except Exception as e:\n",
    "        # Defensive error handling\n",
    "        return {\n",
    "            \"role\": \"tool\",\n",
    "            \"tool_call_id\": tool_call.id,\n",
    "            \"content\": json.dumps({\n",
    "                \"error\": str(e),\n",
    "                \"function\": function_name,\n",
    "                \"args\": tool_call.function.arguments\n",
    "            })\n",
    "        }\n",
    "\n",
    "def process_tool_calls(message):\n",
    "    \"\"\"\n",
    "    Process all tool calls from the AI response.\n",
    "    Returns tool responses and extracted metadata.\n",
    "    \"\"\"\n",
    "    responses = []\n",
    "    metadata = {\n",
    "        \"cities\": set(),\n",
    "        \"caregiver_ids\": set(),\n",
    "        \"total_results\": 0\n",
    "    }\n",
    "    \n",
    "    if not message.tool_calls:\n",
    "        return responses, metadata\n",
    "    \n",
    "    for tool_call in message.tool_calls:\n",
    "        # Execute the tool call\n",
    "        response = execute_tool_call(tool_call)\n",
    "        responses.append(response)\n",
    "        \n",
    "        # Extract metadata for UI enhancements\n",
    "        try:\n",
    "            import json\n",
    "            content = json.loads(response[\"content\"])\n",
    "            \n",
    "            # Extract cities from search results\n",
    "            if \"results\" in content and isinstance(content[\"results\"], list):\n",
    "                for item in content[\"results\"]:\n",
    "                    if isinstance(item, dict) and \"city\" in item:\n",
    "                        metadata[\"cities\"].add(item[\"city\"])\n",
    "                    if isinstance(item, dict) and \"id\" in item:\n",
    "                        metadata[\"caregiver_ids\"].add(item[\"id\"])\n",
    "                        \n",
    "            if \"count\" in content:\n",
    "                metadata[\"total_results\"] += content[\"count\"]\n",
    "                \n",
    "        except:\n",
    "            pass  # Silently ignore metadata extraction errors\n",
    "    \n",
    "    return responses, metadata\n",
    "\n",
    "def generate_city_image(city):\n",
    "    \"\"\"\n",
    "    Generate or retrieve a city image (placeholder for future enhancement).\n",
    "    Could integrate with DALL-E, Unsplash API, or local image database.\n",
    "    \"\"\"\n",
    "    # Placeholder - can be enhanced with actual image generation\n",
    "    return None\n",
    "\n",
    "def chat(history):\n",
    "    \"\"\"\n",
    "    Main chat handler with multi-modal support and defensive error handling.\n",
    "    Handles conversation flow, tool calls, and response generation.\n",
    "    \"\"\"\n",
    "    # Normalize history format\n",
    "    history = [{\"role\": h[\"role\"], \"content\": h[\"content\"]} for h in history]\n",
    "    \n",
    "    # Initialize conversation with system prompt\n",
    "    messages = [{\"role\": \"system\", \"content\": system_prompt}] + history\n",
    "    \n",
    "    # Initialize metadata\n",
    "    image = None\n",
    "    selected_city = None\n",
    "    \n",
    "    try:\n",
    "        # Initial API call\n",
    "        response = openai.chat.completions.create(\n",
    "            model=MODEL,\n",
    "            messages=messages,\n",
    "            tools=tools\n",
    "        )\n",
    "        \n",
    "        # Tool calling loop (with safety limit)\n",
    "        max_iterations = 5\n",
    "        iteration = 0\n",
    "        \n",
    "        while response.choices[0].finish_reason == \"tool_calls\" and iteration < max_iterations:\n",
    "            iteration += 1\n",
    "            message = response.choices[0].message\n",
    "            \n",
    "            # Process all tool calls\n",
    "            tool_responses, metadata = process_tool_calls(message)\n",
    "            \n",
    "            # Track city for image generation\n",
    "            if metadata[\"cities\"]:\n",
    "                selected_city = list(metadata[\"cities\"])[0]\n",
    "            \n",
    "            # Add assistant message and tool responses to conversation\n",
    "            messages.append(message)\n",
    "            messages.extend(tool_responses)\n",
    "            \n",
    "            # Continue conversation\n",
    "            response = openai.chat.completions.create(\n",
    "                model=MODEL,\n",
    "                messages=messages,\n",
    "                tools=tools\n",
    "            )\n",
    "        \n",
    "        # Extract final reply\n",
    "        reply = response.choices[0].message.content\n",
    "        history.append({\"role\": \"assistant\", \"content\": reply})\n",
    "        \n",
    "        # Generate voice response\n",
    "        voice = announcements(reply)\n",
    "        \n",
    "        # Generate city image if applicable\n",
    "        if selected_city:\n",
    "            image = generate_city_image(selected_city)\n",
    "        \n",
    "        return history, voice, image\n",
    "        \n",
    "    except Exception as e:\n",
    "        # Defensive error handling for the entire chat flow\n",
    "        error_message = f\"I apologize, but I encountered an error: {str(e)}. Please try again.\"\n",
    "        history.append({\"role\": \"assistant\", \"content\": error_message})\n",
    "        return history, None, None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "451ed2e5",
   "metadata": {},
   "source": [
    "## Step 9: The Interface\n",
    "\n",
    "A clean, professional web UI built with Gradio.\n",
    "\n",
    "Features:\n",
    "- Chat interface with conversation history\n",
    "- Voice responses that auto-play\n",
    "- Settings sidebar (model selection, voice options)\n",
    "- Clear instructions for families\n",
    "\n",
    "**Why Gradio?** At MyWoosah, I needed something non-technical staff could use immediately. Gradio made that possible without weeks of frontend work.\n",
    "\n",
    "**Run this cell to launch!** 🚀\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "a07e7793-b8f5-44f4-aded-5562f633271a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "* Running on local URL:  http://127.0.0.1:7871\n",
      "* To create a public link, set `share=True` in `launch()`.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><iframe src=\"http://127.0.0.1:7871/\" width=\"100%\" height=\"500\" allow=\"autoplay; camera; microphone; clipboard-read; clipboard-write;\" frameborder=\"0\" allowfullscreen></iframe></div>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gradio as gr\n",
    "\n",
    "# Gradio UI Setup\n",
    "\n",
    "def put_message_in_chatbot(message, history):\n",
    "    \"\"\"Add user message to chat history\"\"\"\n",
    "    return \"\", history + [{\"role\": \"user\", \"content\": message}]\n",
    "\n",
    "# Custom CSS for better styling\n",
    "custom_css = \"\"\"\n",
    "#chatbot {\n",
    "    border-radius: 10px;\n",
    "    box-shadow: 0 2px 8px rgba(0,0,0,0.1);\n",
    "}\n",
    "#message_box {\n",
    "    border-radius: 8px;\n",
    "}\n",
    ".header {\n",
    "    text-align: center;\n",
    "    padding: 20px;\n",
    "    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);\n",
    "    color: white;\n",
    "    border-radius: 10px;\n",
    "    margin-bottom: 20px;\n",
    "}\n",
    "\"\"\"\n",
    "\n",
    "with gr.Blocks(title=\"CareGiver AI Assistant\", css=custom_css, theme=gr.themes.Soft()) as ui:\n",
    "    \n",
    "    # Header\n",
    "    gr.Markdown(\"\"\"\n",
    "    <div class=\"header\">\n",
    "        <h1>🏥 RoboCare AI Assistant</h1>\n",
    "        <p>Find the perfect caregiver for your loved ones</p>\n",
    "    </div>\n",
    "    \"\"\")\n",
    "    \n",
    "    # Instructions\n",
    "    with gr.Accordion(\"ℹ️ Click here to learn more on how to use this AI\", open=False):\n",
    "        gr.Markdown(\"\"\"\n",
    "        **Tell me what you need:**\n",
    "        - Type of care (elder care, child care, companionship, etc.)\n",
    "        - Location (city, state)\n",
    "        - Schedule requirements (days/times)\n",
    "        - Budget constraints\n",
    "        - Language or certification needs\n",
    "        \n",
    "        **Example:** \"I need an elder care provider in Boston for Monday mornings who speaks Spanish and has CPR certification.\"\n",
    "        \n",
    "        ⚠️ **Note:** This is a demo app. Always verify credentials and conduct background checks independently.\n",
    "        \"\"\")\n",
    "    \n",
    "    # Main chat interface\n",
    "    with gr.Row():\n",
    "        with gr.Column(scale=2):\n",
    "            chatbot = gr.Chatbot(\n",
    "                height=500, \n",
    "                type=\"messages\",\n",
    "                elem_id=\"chatbot\",\n",
    "                label=\"Chat History\",\n",
    "                avatar_images=(None, \"🤖\")\n",
    "            )\n",
    "            \n",
    "            # Audio output\n",
    "            audio_output = gr.Audio(\n",
    "                label=\"Voice Response\",\n",
    "                autoplay=True,\n",
    "                visible=True,\n",
    "                interactive=False\n",
    "            )\n",
    "        \n",
    "        # Settings sidebar\n",
    "        with gr.Column(scale=1):\n",
    "            gr.Markdown(\"### ⚙️ Settings\")\n",
    "            \n",
    "            # Model selector (for future enhancement)\n",
    "            model_select = gr.Dropdown(\n",
    "                choices=[\"gpt-4o-mini\", \"gpt-4o\", \"gpt-4-turbo\"],\n",
    "                value=\"gpt-4o-mini\",\n",
    "                label=\"AI Model\",\n",
    "                interactive=True\n",
    "            )\n",
    "            \n",
    "            # Voice selector\n",
    "            voice_select = gr.Dropdown(\n",
    "                choices=[\"coral\", \"alloy\", \"echo\", \"fable\", \"onyx\", \"nova\", \"shimmer\"],\n",
    "                value=\"coral\",\n",
    "                label=\"Voice\",\n",
    "                interactive=True\n",
    "            )\n",
    "            \n",
    "            # Audio toggle\n",
    "            audio_enabled = gr.Checkbox(\n",
    "                label=\"Enable Voice Responses\",\n",
    "                value=True\n",
    "            )\n",
    "            \n",
    "            # Clear button\n",
    "            clear_btn = gr.Button(\"🗑️ Clear Conversation\", variant=\"secondary\")\n",
    "    \n",
    "    # Input section\n",
    "    with gr.Row():\n",
    "        message = gr.Textbox(\n",
    "            label=\"Your Message\",\n",
    "            placeholder=\"Type your question here... (e.g., 'I need elder care in Boston')\",\n",
    "            lines=2,\n",
    "            elem_id=\"message_box\",\n",
    "            scale=4\n",
    "        )\n",
    "        send_btn = gr.Button(\"Send\", variant=\"primary\", scale=1)\n",
    "    \n",
    "    # Event handlers\n",
    "    def chat_wrapper(history):\n",
    "        \"\"\"Wrapper to handle chat and extract only needed outputs\"\"\"\n",
    "        history_out, voice, image = chat(history)\n",
    "        return history_out, voice\n",
    "    \n",
    "    # Submit on enter or button click\n",
    "    submit_event = message.submit(\n",
    "        put_message_in_chatbot,\n",
    "        inputs=[message, chatbot],\n",
    "        outputs=[message, chatbot]\n",
    "    ).then(\n",
    "        chat_wrapper,\n",
    "        inputs=chatbot,\n",
    "        outputs=[chatbot, audio_output]\n",
    "    )\n",
    "    \n",
    "    send_btn.click(\n",
    "        put_message_in_chatbot,\n",
    "        inputs=[message, chatbot],\n",
    "        outputs=[message, chatbot]\n",
    "    ).then(\n",
    "        chat_wrapper,\n",
    "        inputs=chatbot,\n",
    "        outputs=[chatbot, audio_output]\n",
    "    )\n",
    "    \n",
    "    # Clear conversation\n",
    "    clear_btn.click(\n",
    "        lambda: ([], None),\n",
    "        outputs=[chatbot, audio_output]\n",
    "    )\n",
    "    \n",
    "    # Footer\n",
    "    gr.Markdown(\"\"\"\n",
    "    ---\n",
    "    <center>\n",
    "    <small>Powered by OpenAI & Gradio | Built by RoboOffice Ltd</small>\n",
    "    </center>\n",
    "    \"\"\")\n",
    "\n",
    "# Launch with better configuration\n",
    "ui.launch(\n",
    "    inbrowser=True,\n",
    "    share=False,\n",
    "    show_error=True,\n",
    "    quiet=False\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97d87d95",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Reflections\n",
    "\n",
    "This project started from frustration: *\"There has to be a better way to match families with caregivers.\"*\n",
    "\n",
    "Through the Andela program, I learned that AI + thoughtful engineering = solutions to real problems.\n",
    "\n",
    "### What Worked:\n",
    "- **Function calling** eliminated the need for custom queries\n",
    "- **Prompt engineering** prevented keyword mismatches\n",
    "- **Defensive coding** made it robust\n",
    "- **Gradio** made it accessible\n",
    "\n",
    "### What I'd Do Next:\n",
    "- Add speech input (families could call and talk)\n",
    "- Connect to actual MyWoosah database\n",
    "- Add background check API integration\n",
    "- Deploy for real users\n",
    "\n",
    "### The Bigger Picture:\n",
    "\n",
    "This isn't just about caregiving. The same pattern works for:\n",
    "- Healthcare appointments\n",
    "- Legal services\n",
    "- Tutoring platforms\n",
    "- Any matching problem where natural language beats forms\n",
    "\n",
    "AI doesn't replace good database design—it makes it accessible to everyone.\n",
    "\n",
    "---\n",
    "\n",
    "**For MyWoosah Inc and beyond:** This is proof that AI can transform how we connect people with the care they need.\n",
    "\n",
    "*Built during Week 2 of the Andela LLM Engineering Program*\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
